package com.hefan.notify.dao;

import com.cat.tiger.util.CollectionUtils;
import com.cat.tiger.util.GlobalConstants;
import com.google.common.collect.Maps;
import com.hefan.common.orm.dao.BaseDaoImpl;
import com.hefan.notify.bean.AppInitInfo;
import com.hefan.notify.bean.ListHotIndexVo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * User: criss Date: 16/9/21 Time: 15:20
 */
@Repository
public class ItemStaticDao extends BaseDaoImpl<AppInitInfo> {

	@Resource
	JdbcTemplate jdbcTemplate;

	public List findFee() {

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(" id,item_code type ,price");
		sql.append(" FROM currency_type_price");
		sql.append(" WHERE zf=0 ");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	public List advertiseFee() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(" title,");
		sql.append(" type,");
		sql.append(" image_path imgPath,");
		sql.append(" direct_url directUrl,");
		sql.append(" show_time showTime");
		sql.append(" FROM advertise_power_on");
		sql.append(" WHERE del_flag=0 and state=1 And now()<end_date and now()>start_date");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 * 直播间礼物列表
	 * 
	 * @return
	 */
	public List getLivePresent() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(" id,sort,type,status,present_name,icon,showpic,price,experience,rank,iscontinue,iseffect,isexclusive,unusable_icon,across_sort ");
		sql.append(" FROM present");
		sql.append(" WHERE");
		sql.append(" isputaway=0");
		sql.append(" AND status=0");
		sql.append(" and zf=0 order by sort");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 * 动态礼物
	 * 
	 * @return
	 */
	public List getDynamicPresent() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("id,sort, type,present_name,status,icon,showpic,price,experience,rank,iscontinue,iseffect,isexclusive,unusable_icon,across_sort ");
		sql.append(" FROM present");
		sql.append(" WHERE");
		sql.append(" isputaway=0");
		sql.append(" AND status=1");
		sql.append(" AND zf=0 order by sort");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 * 根据礼物分类获取礼物列表
	 * @param status
	 * @return
     */
	public List getPresentByStatus(int status) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(" id,sort,type,status,present_name,icon,showpic,price,experience,rank,iscontinue,iseffect,isexclusive,unusable_icon,across_sort, ");
		sql.append(" UNIX_TIMESTAMP(update_time) as update_time,zip_url,zip_md5,zip_size ");
		sql.append(" FROM present");
		sql.append(" WHERE");
		sql.append(" isputaway=0");
		sql.append(" AND status=?");
		sql.append(" and zf=0 order by sort");
		return getJdbcTemplate().queryForList(sql.toString(),new Object[] {status});
	}


	/**
	 * version
	 * 
	 * @return
	 */
	public Map getPresentVersion() {
		Map resMap = new HashMap();
		resMap.put("Version", 0);
		resMap.put("CusExsVersion", 0);
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM (");
		sql.append(" SELECT * FROM ");
		sql.append(" app_present_info ORDER BY present_version DESC ");
		sql.append(" ) t GROUP BY type");
		List list = getJdbcTemplate().queryForList(sql.toString());
		if (CollectionUtils.isNotEmpty(list)) {
			for (int i = 0; i < list.size(); i++) {
				Map listMap = (Map) list.get(i);
				if (listMap != null && !listMap.isEmpty() && listMap.containsKey("type")) {
					if (listMap.get("type").toString().equals("0") && listMap.containsKey("present_version")) {
						resMap.put("Version", listMap.get("present_version"));
					} else if (listMap.get("type").toString().equals("1") && listMap.containsKey("present_version")) {
						resMap.put("CusExsVersion", listMap.get("present_version"));
					}
					// }else if(listMap.get("type").toString().equals("2") &&
					// listMap.containsKey("present_version")){
					// resMap.put("BigVersion", listMap.get("present_version"));
					// }
				}
			}
		}
		return resMap;
	}

	/**
	 * BigVersion
	 * 
	 * @return
	 */
	public List getBigVersion() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(" * ");
		sql.append(" FROM present_effect");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	public List getRankingList() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" wu.user_id id, ");
		sql.append(" wu.nick_name name, ");
		sql.append(" wu.user_type type, ");
		sql.append(" wu.head_img headImg ");
		sql.append(" FROM web_user wu  ");
		sql.append(" WHERE ");
		sql.append(" wu.superior_contract_state = 0  ");
		sql.append(" AND wu.state  = 0 ");
		sql.append(" AND wu.contract_state = 0 ");
		sql.append(" AND wu.superior_state = 0 ");
		sql.append(" AND wu.is_del = 0 ");
		sql.append(" AND wu.user_type = 1 ");
		sql.append(" AND wu.hefan_total != 0 ");
		sql.append(" ORDER BY  hefan_total desc");
		sql.append(" LIMIT 0,5 ");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	public void updatePresentVersion(int i) {
		StringBuilder sql = new StringBuilder();
		sql.append(" INSERT INTO app_present_info  (present_version,create_time,type) VALUES (" + i + ",NOW(),0) ");
		getJdbcTemplate().update(sql.toString());
	}

	public Map getAppInitInfo(int type) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT");
		sql.append(
				" type,app_version,update_link, is_force isForce, update_info updateInfo,version_name versionName, ");
		sql.append(" IFNULL(( select app_version FROM app_init_info WHERE type=" + type
				+ " and  is_force = 1  and  delete_flag=0 ORDER BY create_time DESC LIMIT 1),'1') latestforceVersion");
		sql.append(" from app_init_info ");
		sql.append(" WHERE type=" + type + " and  delete_flag=0 ORDER BY create_time DESC LIMIT 1");
		return getJdbcTemplate().queryForMap(sql.toString());
	}

	public List<ListHotIndexVo> listHostIndexTop() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT wu.user_id userId");
		sql.append(" FROM live_room lr,web_user wu ");
		sql.append(" WHERE lr.user_id=wu.user_id and lr.type IN (2,3) and`status`=1 ORDER BY lr.start_time");
		return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(ListHotIndexVo.class));
	}

	public List<ListHotIndexVo> listHostIndexUnder() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT wu.user_id userId");
		sql.append(" FROM live_room lr,web_user wu ");
		sql.append(" WHERE lr.user_id=wu.user_id and lr.type IN (1) and`status`=1 ");
		return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(ListHotIndexVo.class));
	}

	public List listHostIndex() {
		StringBuilder sql = new StringBuilder();
		sql.append(
				" (SELECT wu.user_id id,IFNULL(wu.head_img,'') headImg,IFNULL(wu.nick_name,'') name,lr.type,IFNULL(lr.live_img,'') liveImg,IFNULL((SELECT llg.location from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') location,IFNULL(lr.live_name,'') personSign,lr.chat_room_id chatRoomId,IFNULL((SELECT llg.pull_url from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') liveUrl,IFNULL((SELECT llg.live_uuid from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') liveUuid,IFNULL(ll.display_graph,'') displayGraph,(SELECT COUNT(*) from live_log_person llp where llp.chat_room_id=lr.chat_room_id GROUP BY chat_room_id) peopleCount");
		sql.append(" FROM(live_room lr,web_user wu)");
		sql.append(
				" LEFT JOIN live_log ll ON ll.chat_room_id=lr.chat_room_id WHERE lr.user_id=wu.user_id and lr.type IN (2,3)  and `status`=1  GROUP BY lr.chat_room_id  ORDER BY lr.start_time)");
		sql.append(" UNION");

		sql.append(
				" (SELECT wu.user_id id,IFNULL(wu.head_img,'') headImg,IFNULL(wu.nick_name,'') name,lr.type,IFNULL(lr.live_img,'') liveImg,IFNULL((SELECT llg.location from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') location,IFNULL(lr.live_name,'') personSign,lr.chat_room_id chatRoomId,IFNULL((SELECT llg.pull_url from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') liveUrl,IFNULL((SELECT llg.live_uuid from live_log llg where ll.chat_room_id=llg.chat_room_id   ORDER BY llg.start_time desc LIMIT 1),'') liveUuid,IFNULL(ll.display_graph,'') displayGraph,(SELECT COUNT(*) from live_log_person llp where llp.chat_room_id=lr.chat_room_id GROUP BY chat_room_id) peopleCount");
		sql.append(" FROM(live_room lr,web_user wu)");
		sql.append(
				" LEFT JOIN live_log ll ON ll.chat_room_id=lr.chat_room_id WHERE lr.user_id=wu.user_id and lr.type =1  and `status`=1  GROUP BY lr.chat_room_id  ORDER BY people_count)");
		List list = getJdbcTemplate().queryForList(sql.toString());
		return list;
	}

	/**
	 * 获取所有正在直播的直播间人数
	 * 
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List peopleCount() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) peopleCount, lr.chat_room_id chatRoomId from live_log_person llp, live_room lr "
				+ " where llp.chat_room_id=lr.chat_room_id and lr.status=" + GlobalConstants.AUTHOR_LIVING
				+ " GROUP BY lr.chat_room_id");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	public List banner() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" ws.title, ");
		sql.append(" ws.source img, ");
		sql.append(" ws.type type, ");
		sql.append(" ws.forward url, ");
		sql.append(" ws.is_show isShow ");
		sql.append(" FROM web_slider ws  ");
		sql.append(" WHERE ");
		sql.append(" zf=0  ");
		sql.append(" ORDER BY  sort ");
		return getJdbcTemplate().queryForList(sql.toString());
	}

	public List listNewLive() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" wu.user_id id, ");
		sql.append(" wu.nick_name name, ");
		sql.append(" lr.type type, ");
		sql.append(" lr.location location, ");
		sql.append(" lr.chat_room_id chatRoomId, ");
		sql.append(" lr.live_img liveImg, ");
		sql.append(" lr.live_name personSign, ");
		sql.append(" wu.head_img headImg, ");
		sql.append(" ll.pull_url liveUrl, ");
		sql.append(" ll.live_uuid liveUuid, ");
		sql.append(" ll.display_graph displayGraph ");
		sql.append(" FROM ");
		sql.append(" live_room lr, ");
		sql.append(" web_user wu , ");
		sql.append(" live_log ll ");
		sql.append(" WHERE ");
		sql.append(" lr.user_id = wu.user_id ");
		sql.append(" AND ll.user_id = lr.user_id ");
		sql.append(" AND lr.status=1 ");
		sql.append(" AND lr.stuff=0 ");
		sql.append(" AND ll.end_time IS NULL ");
		sql.append(" ORDER BY ll.start_time DESC");
		sql.append(" LIMIT 0,15 ");
		return queryMap(sql.toString());
	}

	/**
	 * 粉丝贡献榜
	 * 
	 * @param userId
	 * @return
	 */
	public List<Map<String, Object>> fansContributionList(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" wu.nick_name name, ");
		sql.append(" wu.sex sex, ");
		sql.append(" wu.user_level level , ");
		sql.append(" wu.user_type type, ");
		sql.append(" wu.head_img headImg , ");
		sql.append(" wu.auth_id authId, ");
		sql.append(" SUM(wue.fanpiao_num) fanpiaoNum, ");
		sql.append(" wu.user_id userId ");
		sql.append(" FROM ");
		sql.append(" web_user wu ,web_user_expend wue ");
		sql.append(" WHERE ");
		sql.append(" wu.user_id = wue.user_id ");
		sql.append(" AND wu.state = 0 ");
		sql.append(" AND wu.user_type != 4 ");
		sql.append(" AND wu.is_del = 0 ");
		sql.append(" AND wue.to_user_id =? ");
		sql.append(" AND wue.source!= 2 ");
		sql.append(" GROUP BY wue.user_id ");
		sql.append(" ORDER BY fanpiaoNum ");
		sql.append(" DESC LIMIT 0,500");
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), userId);
		return list;
	}

	public List<String> getAllAnchorUserId() {
		String sql = "SELECT wu.user_id userId FROM web_user wu WHERE wu.user_type IN(1,2,3) AND wu.is_del = 0 ";
		return jdbcTemplate.queryForList(sql, null, String.class);
	}

	/**
	 * 获取除明星以外的所有人
	 * @return
     */
	public List<String> getAllExcludeAnchorUserId() {
		String sql = "SELECT wu.user_id userId FROM web_user wu WHERE wu.user_type NOT IN(1,2,3) AND wu.is_del = 0 ";
		return jdbcTemplate.queryForList(sql, null, String.class);
	}

	public Integer hefanCountByUserId(String id) {
		String sql = "SELECT wu.hefan_total hefanNum FROM web_user wu WHERE wu.user_id=?";
		Object[] params = new Object[] { id };
		List<Integer> list = jdbcTemplate.queryForList(sql, params, Integer.class);
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return 0;
		}
	}

	public List joinLiveRoomWord() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT t.content content FROM message_live_room t WHERE t.is_del = 0 ORDER BY t.sort  LIMIT 0,5 ");
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		return list;
	}

	/**
	 * 获取自定义表情列表（自定义表情只增加，软删的也要同步过去，以方便查看历史）
	 *
	 * @Title: getCustomExpressionList
	 * @Description: TODO(这里用一句话描述这个方法的作用)
	 * @return
	 * @return: List
	 * @author: LiTeng
	 * @throws @date:
	 *             2016年11月8日 下午5:14:46
	 */
	public List getCustomExpressionList() {
		String sql = "select id,expression_name as expressionName,expression_url expressionUrl,"
				+ " expression_self_encoded expressionSelfEncoded,delete_flag deleteFlag from custom_expression";
		return jdbcTemplate.queryForList(sql.toString());
	}

	public Map liveNotice() {
		String sql = " SELECT an.img_path pic ,an.img_url url FROM advance_notice an ORDER BY an.update_time DESC LIMIT 0,1 ";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return Maps.newHashMap();
		}
	}

	/**
	 * 直播预约列表
	 *
	 * @return
	 */
	public List liveBookingList() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  ");
		sql.append(" anu.id liveNoticeId, ");// 预约直播id
		sql.append(" anu.img_path noticePic, ");// 预约图片
		sql.append(" anu.name noticeName, ");// 预约直播名称
		sql.append(" anu.user_id noticeUserId , ");// 预约主播id
		sql.append(" DATE_FORMAT(anu.start_time,'%Y/%m/%d') noticeDate , ");// 预约日期%Y/%m/%d
		sql.append(" DATE_FORMAT(anu.start_time,'%k:%i') noticeTime ");// 预约时间%k:%i
		sql.append(" FROM advance_notice_user anu ");
		sql.append(" WHERE");
		sql.append(" anu.start_time >curdate() ");
		sql.append(" AND anu.delete_flag = 0 ");
		sql.append(" ORDER BY  start_time ");
		return jdbcTemplate.queryForList(sql.toString());
	}

	public List<String> getSensitivewordList() {
		String sql = "select content from badword where zf=0 and IFNULL(content,'') !='' ORDER BY content";
		return jdbcTemplate.queryForList(sql, null, String.class);
	}
}
